using System;
using System.Collections.Generic;
using System.Text;
using Sinacor.Infra.Service.Data;
using System.Data.Common;
using System.Data;
using Sinacor.Infra.Service.Security.Authorization;
using Sinacor.Servicos.Comum.Util.SGBD;


namespace Sinacor.Servico.Bovespa.Cliente.ResourceAccess
{
    public class Correspondente : BaseResourceAccess
    {
        public Correspondente()
            : base(AuthorizationContext.Current.CompanyId)
        {
        }
        /// Data:04/02/2008 15:47
        /// Autor:DMilanez.7Comm
        /// Obs:
        /// Correcao
        /// Metodo nao estava implementado
        /// 
        /// Data:12/02/2008 14:10
        /// Autor:DMilanez.7Comm
        /// Obs:
        /// Alterado
        public bool VerificarExigenciaDigitacao(string codigoBolsa, int codigoEmpresa)
        {
            OracleDataBase db = this.DataBase; // new OracleDataBase(AuthorizationContext.Current.CompanyId);
            DbCommand dbCommand;
            string ret;
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT IN_MEMB_BOLSA ");
            sql.Append("FROM TGELOCAL ");
            sql.Append("WHERE CD_EMPRESA = :CD_EMPRESA ");
            sql.Append("AND CD_BOLSA   = :CD_BOLSA");

            dbCommand = db.GetSqlStringCommand(sql.ToString());

            db.AddInParameter(dbCommand, "CD_EMPRESA", DbType.Int32, codigoEmpresa);
            db.AddInParameter(dbCommand, "CD_BOLSA", DbType.AnsiString, codigoBolsa);

            ret = Convert.ToString(db.ExecuteScalar(dbCommand));

            return (ret == "N");

        }

        /// Data:11/02/2008 15:59
        /// Autor:DMilanez.7Comm
        /// Obs:
        /// 
        /// Data:12/02/2008 14:10
        /// Autor:DMilanez.7Comm
        /// Obs:
        /// Query Alterada e  metodo renomeado
        public bool VerificarSePodeExistirDigitacaoCorrespondente()
        {
            OracleDataBase db = this.DataBase; // new OracleDataBase(AuthorizationContext.Current.CompanyId);

            DbCommand dbCommand;
            string ret;
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT IN_CORRESPONDENTE ");
            sql.Append("FROM TBOPARAM ");
            dbCommand = db.GetSqlStringCommand(sql.ToString());
            ret = Convert.ToString(db.ExecuteScalar(dbCommand));

            return (ret == "S");
        }

        /// Data:04/02/2008 15:57
        /// Autor:DMilanez.7Comm
        /// Obs:
        /// Correcao
        /// Metodo nao estava implementado
        public Decimal? RecuperarPercentualComissao(int codigoCorrespondente)
        {
            OracleDataBase db = this.DataBase; // new OracleDataBase(AuthorizationContext.Current.CompanyId);

            DbCommand dbCommand;
            Object ret;
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT PC_COMISSAO ");
            sql.Append("FROM TBOCORRESP ");
            sql.Append("WHERE CD_CORRESP = :CD_CORRESP ");
            sql.Append("AND NVL(IN_BROKER,'N') = 'N'");

            dbCommand = db.GetSqlStringCommand(sql.ToString());

            db.AddInParameter(dbCommand, "CD_CORRESP", DbType.Int32, codigoCorrespondente);

            ret = db.ExecuteScalar(dbCommand);

            if (ret == null || ret == DBNull.Value)
                return null;
            else
                return Convert.ToDecimal(ret);
        }

        public bool VerificarExistencia(int codigoCorrespondente)
        {
            OracleDataBase db = this.DataBase; // new OracleDataBase(AuthorizationContext.Current.CompanyId);

            DbCommand dbCommand;
            Object ret;
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT 1 ");
            sql.Append("FROM TBOCORRESP ");
            sql.Append("WHERE CD_CORRESP = :CD_CORRESP ");
            sql.Append("AND NVL(IN_BROKER,'N') = 'N'");

            dbCommand = db.GetSqlStringCommand(sql.ToString());

            db.AddInParameter(dbCommand, "CD_CORRESP", DbType.Int32, codigoCorrespondente);

            ret = db.ExecuteScalar(dbCommand);

            if (ret == null || ret == DBNull.Value)
                return false;
            else
                return true;
        }


        /// Data:20/04/2008 17:45
        /// Autor:DMilanez.7Comm
        /// Obs: Metodo responsavel por consultar um correspondente
        public BusinessEntity.Correspondente Consultar(BusinessEntity.Correspondente request)
        {
            OracleDataBase db = this.DataBase; // new OracleDataBase(AuthorizationContext.Current.CompanyId);
            BusinessEntity.Correspondente response = null;
            DbCommand dbCommand;
            IDataReader reader;
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT CD_CORRESP, ");
            sql.Append(" NM_CORRESP, ");
            sql.Append(" NM_RESU_CORRESP, ");
            sql.Append(" PC_COMISSAO, ");
            sql.Append(" CD_MEMBRO_BOLSA, ");
            sql.Append(" IN_BROKER ");
            sql.Append("FROM TBOCORRESP ");
            sql.Append("WHERE CD_CORRESP = :CD_CORRESP ");
            sql.Append("AND NVL(IN_BROKER,'N') = 'N'");

            dbCommand = db.GetSqlStringCommand(sql.ToString());

            db.AddInParameter(dbCommand, "CD_CORRESP", DbType.Int32, request.Codigo);

            reader = db.ExecuteReader(dbCommand);

            if (reader.Read())
            {
                response = new BusinessEntity.Correspondente();
                response.Codigo = Sinacor.Servicos.Comum.Util.SGBD.Funcoes.BDValorInt32(reader["CD_CORRESP"], true);
                response.CodigoMembroBovespa = Sinacor.Servicos.Comum.Util.SGBD.Funcoes.BDValorInt32(reader["CD_MEMBRO_BOLSA"], true);
                response.IndicadorBroker = Sinacor.Servicos.Comum.Util.SGBD.Funcoes.BDValorString(reader["IN_BROKER"]) == "S";
                response.Nome = Sinacor.Servicos.Comum.Util.SGBD.Funcoes.BDValorString(reader["NM_CORRESP"], true);
                response.NomeResumidoCorrespondente = Sinacor.Servicos.Comum.Util.SGBD.Funcoes.BDValorString(reader["NM_RESU_CORRESP"], true);
                response.PercentualComissao = Sinacor.Servicos.Comum.Util.SGBD.Funcoes.BDValorDecimal(reader["PC_COMISSAO"], true);
            }

            reader.Close();//20080421 16:17 - Nao estava fechando o reader - DMilanez.7Comm

            return response;
        }





        public void AlterarCorrespondente(BusinessEntity.Correspondente request)
        {
            BusinessEntity.Correspondente response = new Sinacor.Servico.Bovespa.Cliente.BusinessEntity.Correspondente();
            StringBuilder sql = new StringBuilder();
            sql.Append("UPDATE TBOCORRESP ");
            sql.Append("SET  ");
            sql.Append("NM_CORRESP = :Nome, ");
            sql.Append("NM_RESU_CORRESP = :NomeResumido, ");
            sql.Append("PC_COMISSAO = :PercentualComissao, ");
            sql.Append("CD_MEMBRO_BOLSA = :CodigoMembro, ");
            sql.Append("IN_BROKER = :IndicadorBroker ");
            sql.Append("WHERE ");
            sql.Append("CD_CORRESP = :Codigo ");
            using (DbCommand command = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
                this.DataBase.AddInParameter(command, "Codigo", DbType.Int32, request.Codigo);
                this.DataBase.AddInParameter(command, "Nome", DbType.String, request.Nome);
                this.DataBase.AddInParameter(command, "NomeResumido", DbType.String, request.NomeResumidoCorrespondente);
                this.DataBase.AddInParameter(command, "PercentualComissao", DbType.String, request.PercentualComissao);
                this.DataBase.AddInParameter(command, "CodigoMembro", DbType.Int32, request.CodigoMembroBovespa);
                this.DataBase.AddInParameter(command, "IndicadorBroker", DbType.String, request.IndicadorBroker == true ? "S" : "N");
                this.DataBase.ExecuteNonQuery(command);

            }
        }

        //Data :19/08/2009
        //Autor: Felipe Barbosa
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>

        public List<BusinessEntity.Correspondente> ConsultarCorrespondentes(BusinessEntity.Parameter.FiltroCorrespondente filtro)
        {
            List<BusinessEntity.Correspondente> response = new List<Sinacor.Servico.Bovespa.Cliente.BusinessEntity.Correspondente>();
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT  ");
            sql.Append("CD_CORRESP AS Codigo, ");
            sql.Append("NM_CORRESP as Nome, ");
            sql.Append("NM_RESU_CORRESP as NomeResumido, ");
            sql.Append("PC_COMISSAO as PercentualComissao, ");
            sql.Append("CD_MEMBRO_BOLSA as CodigoMembroBovespa, ");
            sql.Append("IN_BROKER as IndicadorBroker ");
            sql.Append("FROM TBOCORRESP ");
            sql.Append("where 1=1 ");

            if (!string.IsNullOrEmpty(filtro.Nome))
            {
                sql.Append("AND NM_CORRESP like '%'||:Nome||'%' ");
            }

            if (!string.IsNullOrEmpty(filtro.NomeResumidoCorrespondente))
            {
                sql.Append("AND NM_RESU_CORRESP like '%' + : NomeResumido + '%' ");
            }

            if (filtro.Codigo != null)
            {
                sql.Append("AND CD_CORRESP = :Codigo ");
            }

            if (filtro.CodigoMembroBovespa != null)
            {
                sql.Append("AND CD_MEMBRO_BOLSA = :CodigoMembroBovespa ");
            }

            if (filtro.PercentualComissao != null)
            {
                sql.Append("AND PC_COMISSAO = :PercentualComissao ");
            }
            sql.Append(" ORDER BY CD_CORRESP ");

            using (DbCommand command = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
                if (!string.IsNullOrEmpty(filtro.Nome))
                {
                    this.DataBase.AddInParameter(command, "Nome", DbType.String, filtro.Nome);
                }

                if (!string.IsNullOrEmpty(filtro.NomeResumidoCorrespondente))
                {
                    this.DataBase.AddInParameter(command, "NomeResumido", DbType.Decimal, filtro.NomeResumidoCorrespondente);
                }
                if (filtro.Codigo != null)
                {
                    this.DataBase.AddInParameter(command, "Codigo", DbType.Int32, filtro.Codigo);
                }

                if (filtro.CodigoMembroBovespa != null)
                {
                    this.DataBase.AddInParameter(command, "CodigoMembro", DbType.Int32, filtro.CodigoMembroBovespa);
                }

                if (filtro.PercentualComissao != null)
                {
                    this.DataBase.AddInParameter(command, "PercentualComissao", DbType.String, filtro.PercentualComissao);
                }
                response = this.FindAll<BusinessEntity.Correspondente>(command, new ConstructEntity<BusinessEntity.Correspondente>(ConsultarCorrespondentes));

            }
            return response;
        }
        protected BusinessEntity.Correspondente ConsultarCorrespondentes(IDataReader reader)
        {
            BusinessEntity.Correspondente entity = new BusinessEntity.Correspondente();
            entity.Codigo = reader.GetValue<Int32?>("Codigo");
            entity.CodigoMembroBovespa = reader.GetValue<Int32?>("CodigoMembroBovespa");
            entity.IndicadorBroker = (reader.GetValue<String>("IndicadorBroker") == "S") ? true : false;
            entity.Nome = reader.GetValue<String>("Nome");
            entity.NomeResumidoCorrespondente = reader.GetValue<String>("NomeResumido");
            entity.PercentualComissao = reader.GetValue<Decimal?>("PercentualComissao");
            return entity;
        }
        public void ExcluirCorrespondente(BusinessEntity.Parameter.FiltroCorrespondenteChave request)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(" DELETE FROM TBOCORRESP  ");
            sql.Append(" WHERE CD_CORRESP = :Codigo ");
            using (DbCommand command = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
                this.DataBase.AddInParameter(command, "Codigo", DbType.Int32, request.Codigo);
                this.DataBase.ExecuteNonQuery(command);
            }
        }

        public void IncluirCorrespondente(BusinessEntity.Correspondente request)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("INSERT INTO TBOCORRESP  ");
            sql.Append("(CD_CORRESP,NM_CORRESP,NM_RESU_CORRESP,PC_COMISSAO,CD_MEMBRO_BOLSA,IN_BROKER)  ");
            sql.Append("VALUES  ");
            sql.Append("(:Codigo ,:Nome ,:NomeResumido ,:PercentualComissao ,:CodigoMembro ,:IndicadorBroker)  ");
            using (DbCommand command = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
                this.DataBase.AddInParameter(command, "Codigo", DbType.Int32, request.Codigo);
                this.DataBase.AddInParameter(command, "Nome", DbType.String, request.Nome);
                this.DataBase.AddInParameter(command, "NomeResumido", DbType.String, request.NomeResumidoCorrespondente);
                this.DataBase.AddInParameter(command, "PercentualComissao", DbType.Decimal, request.PercentualComissao);
                this.DataBase.AddInParameter(command, "CodigoMembro", DbType.Int32, request.CodigoMembroBovespa);
                this.DataBase.AddInParameter(command, "IndicadorBroker", DbType.String, request.IndicadorBroker == true ? "S" : "N");
                this.DataBase.ExecuteNonQuery(command);
            }

        }

        public BusinessEntity.Correspondente ObterCorrespondente(BusinessEntity.Parameter.FiltroCorrespondenteChave request)
        {
            BusinessEntity.Correspondente response = new Sinacor.Servico.Bovespa.Cliente.BusinessEntity.Correspondente();
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT  ");
            sql.Append("CD_CORRESP AS Codigo,  ");
            sql.Append("NM_CORRESP as Nome, ");
            sql.Append("NM_RESU_CORRESP as NomeResumido,  ");
            sql.Append("PC_COMISSAO as PercentualComissao, ");
            sql.Append("CD_MEMBRO_BOLSA as CodigoMembroBovespa, ");
            sql.Append("IN_BROKER as IndicadorBroker ");
            sql.Append("FROM TBOCORRESP ");
            sql.Append("WHERE CD_CORRESP = :Codigo ");
            using (DbCommand command = this.DataBase.GetSqlStringCommand(sql.ToString()))
            {
                this.DataBase.AddInParameter(command, "Codigo", DbType.Int32, request.Codigo);
                response = this.FindOne<BusinessEntity.Correspondente>(command, new ConstructEntity<BusinessEntity.Correspondente>(ConsultarCorrespondentes));
            }
            return response;
        }

        protected BusinessEntity.Correspondente ObterCorrespondente(IDataReader reader)
        {
            BusinessEntity.Correspondente entity = new BusinessEntity.Correspondente();
            entity.Codigo = reader.GetValue<int?>("Codigo");
            entity.Nome = reader.GetValue<string>("Nome");
            entity.NomeResumidoCorrespondente = reader.GetValue<string>("NomeResumido");
            entity.PercentualComissao = reader.GetValue<decimal?>("PercentualComissao");
            entity.CodigoMembroBovespa = reader.GetValue<int?>("CodigoMembroBovespa");
            entity.IndicadorBroker = (reader.GetValue<string>("IndicadorBroker") == "S") ? true : false; ;
            return entity;
        }
    }
}

